--select * from fn_get_diebank_lot('QC7506.1', '11264-02')

Create function fn_get_diebank_lot
(
	@LOT_NUMBER 	VARCHAR(50),
	@PART_NUMBER 	VARCHAR(50)
)

	 RETURNS  @FN_RESULT TABLE( Qty_In INT )
AS

BEGIN
	DECLARE @QTY_IN INT
	DECLARE @ASSM_RELEASE_LOT_NUM VARCHAR(50)
	
	SELECT @QTY_IN = (SUM( ISNULL( BIN_1_BALANCE_QTY, 0 ) ) + 
			SUM( ISNULL( BIN_2_BALANCE_QTY,  0 ) ) + 
			SUM( ISNULL( BIN_3_BALANCE_QTY,  0 ) ) + 
			SUM( ISNULL( BIN_4_BALANCE_QTY,  0 ) ) + 
			SUM( ISNULL( BIN_5_BALANCE_QTY,  0 ) ) +
			SUM( ISNULL( BIN_6_BALANCE_QTY,  0 ) ) + 
			SUM( ISNULL( BIN_7_BALANCE_QTY,  0 ) ) + 
			SUM( ISNULL( BIN_8_BALANCE_QTY,  0 ) ) + 
			SUM( ISNULL( BIN_9_BALANCE_QTY,  0 ) ) + 
			SUM( ISNULL( BIN_10_BALANCE_QTY, 0 ) ) + 
			SUM( ISNULL( BIN_11_BALANCE_QTY, 0 ) ) +
			SUM( ISNULL( BIN_12_BALANCE_QTY, 0 ) ) + 
			SUM( ISNULL( BIN_13_BALANCE_QTY, 0 ) ) + 
			SUM( ISNULL( BIN_14_BALANCE_QTY, 0 ) ) + 
			SUM( ISNULL( BIN_15_BALANCE_QTY, 0 ) ) ) 
	FROM LOT_TABLE LT, SORT_BIN_RELEASE_TABLE SRBT, PRODUCT P WHERE LT.LOT_NUMBER = @LOT_NUMBER AND P.PART_NUMBER = @PART_NUMBER AND P.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
 	AND LT.LOT_ID = SRBT.LOT_ID AND LOCATION_ID IN ( SELECT LOCATION_ID FROM LOCATION WHERE L_TYPE = 'SORT' AND L_VENDOR_NAME = 'ATK4')
	
	
	/* to get the assembly lot number */
	SELECT @ASSM_RELEASE_LOT_NUM = LOT_NUMBER FROM ASSM_RELEASE_TABLE WHERE LOT_NUMBER = @LOT_NUMBER

	IF @ASSM_RELEASE_LOT_NUM IS NULL
	BEGIN
	
		INSERT INTO @FN_RESULT VALUES (@QTY_IN) 
	END 

RETURN	

END